"""
数据库连接和会话管理
"""

from typing import AsyncGenerator

from sqlalchemy import create_engine
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker
from sqlalchemy.orm import sessionmaker, Session

from config import settings
from models.base import Base

# 同步数据库引擎（用于Alembic迁移）
db_config = settings.database_config.copy()
db_config.pop('url', None)  # 移除可能冲突的url参数
sync_engine = create_engine(
    settings.database_url,
    **db_config
)

# 临时使用同步引擎
# 异步会话工厂
AsyncSessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=sync_engine
)


def get_sync_db() -> Session:
    """获取同步数据库会话"""
    db = SyncSessionLocal()
    try:
        yield db
    finally:
        db.close()


async def get_async_db():
    """获取异步数据库会话"""
    db = AsyncSessionLocal()
    try:
        yield db
    finally:
        db.close()

# 同步会话工厂
SyncSessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=sync_engine
)


def get_sync_db() -> Session:
    """获取同步数据库会话"""
    db = SyncSessionLocal()
    try:
        yield db
    finally:
        db.close()


def create_tables() -> None:
    """创建所有表"""
    Base.metadata.create_all(bind=sync_engine)


def drop_tables() -> None:
    """删除所有表"""
    Base.metadata.drop_all(bind=sync_engine)


async def create_tables_async() -> None:
    """同步创建所有表（临时使用）"""
    create_tables()


async def drop_tables_async() -> None:
    """同步删除所有表（临时使用）"""
    drop_tables()


def get_db_session() -> Session:
    """获取数据库会话（同步）"""
    return SyncSessionLocal()


async def get_db_session_async() -> Session:
    """获取数据库会话（同步，临时使用）"""
    return SyncSessionLocal()


class DatabaseManager:
    """数据库管理器"""

    def __init__(self):
        self.sync_engine = sync_engine
        self.async_engine = None  # 临时为None

    def get_sync_session(self) -> Session:
        """获取同步会话"""
        return self.sync_engine.session()

    async def get_async_session(self) -> Session:
        """获取同步会话（临时使用）"""
        return self.sync_engine.session()

    def close_sync_connections(self) -> None:
        """关闭同步连接"""
        self.sync_engine.dispose()

    async def close_async_connections(self) -> None:
        """关闭同步连接（临时使用）"""
        self.sync_engine.dispose()

    async def health_check(self) -> bool:
        """健康检查"""
        try:
            with self.sync_engine.connect() as conn:
                conn.execute("SELECT 1")
            return True
        except Exception:
            return False

    async def get_database_info(self) -> dict:
        """获取数据库信息"""
        try:
            with self.sync_engine.connect() as conn:
                result = conn.execute(
                    "SELECT version() as version, "
                    "current_database() as database, "
                    "current_user as user"
                )
                row = result.fetchone()
                return {
                    "version": row[0] if row else "Unknown",
                    "database": row[1] if row else "Unknown",
                    "user": row[2] if row else "Unknown",
                    "status": "connected"
                }
        except Exception as e:
            return {
                "status": "error",
                "error": str(e)
            }


# 全局数据库管理器实例
db_manager = DatabaseManager()